Configure a Query Export Definition

Loyalty allows you to use the Advanced Export feature to export records selected by an Analytical Query, and to send them to a Secure FTP server, an Amazon S3 Bucket, a Google Cloud Storage Bucket, or your local PC.

This article assumes that you have already created the Export Definition, either by creating it from scratch, or by copying an existing Export Definition (see Create an Export Definition for more information). If creating the Export Definition from scratch, be sure to select the following:

• Connector: Secure FTP, Amazon S3, or Google Cloud Storage

• Table: Query

To configure a Query Export Definition:

1. Select Admin from the top navigation bar, then select Integrations > Exports from the side navigation menu. The Exports screen is displayed.

2. On the Exports screen, search for the desired Export Definition (see Search for an Export Definition for more information).

3. Click the name of the desired Export Definition. The Export Definition Details screen is displayed.

The Export Definition Details screen is split into multiple steps, identified by a series of numbered items across the top of the screen. You can complete these steps (described below) in any order you like. The Next button at the bottom of the screen navigates to the next step in the sequence, and the Back button navigates to the previous step.

Step 1: Query

The Query step is used to select the desired Analytical Query.

1. At the top of the Export Definition Details screen, click Query.

2. From the Bigdata Query drop-down menu, select the desired Analytical Query.

3. Click Save. Or click Next to move to the next step in the Export Definition screen.

Note: The Map & Transform step is not displayed for Query Export Definitions, as the Query itself will define the columns to be exported.

Step 2: Destination

The Destination step is used to define the destination of the export file. This step requires that you have a Connection Profile defined. For more information on creating a Connection Profile, please see Connectors.

1. At the top of the Export Definition Details screen, click Destination.

2. From the Connection Profile drop-down menu, select a Connection Profile.

3. In the File Path field, enter the path to the folder on the FTP server, Amazon S3 Bucket, or Google Cloud Storage Bucket where you want to save the export file.

4. In the Filename field, enter the name of the export file.

Note: The file should be named so that it is unique each time it exports, so a new file doesn't overwrite a previous file. The best practice recommendation is to add a timestamp to the filename, such as {(yyyyMMdd)}.

5. From the Delimiter drop-down menu, select a delimiter character (Comma or Tab), or select Custom and enter the custom character.

6. Optionally, from the Text Qualifier drop-down menu, select a text qualifier character (double quotes, single quotes, or pipe), or select Custom and enter the custom character.

7. From the Line End drop-down menu, select the method used to mark a line break in the export file: LF (Line Feed) or CRLF (Carriage Return and Line Feed).

Note: The Item field has been deprecated and should be left blank.

8. If you want the export file to include a column header row, select Yes from the Include Headers drop-down menu.

9. From the File Encoding drop-down menu, select an encoding method (the default method is UTF-8).

10. Optionally, to export a Control File along with the export file, enter the desired Control File name in the Control File Name field. The Control File contains the expected record count of the export file. The filename must have an extension of either.control or .ctrl .

11. Optionally, to PGP-encrypt the export file, select the desired Encryption Key from the Encrypt With drop-down menu.

12. If you want to allow the platform to export empty files, select Yes from the Allow Empty Files drop-down menu.

13. Click Save.

14. Click Next to move to the next step in the Export Definition Details screen.

Step 3: Schedule

Export Definitions can be executed on-demand (see Execute an Export Definition for more details on that process).

In addition, you can create a schedule that executes the export once at a specified date / time, or on a recurring basis. These schedule options are described below.

Execute Only Once

To schedule an Export Definition to execute once at a specified date / time:

1. At the top of the Export Definition Details screen, click Schedule.

2. Toggle Enabled to Yes.

3. From the Recurrence drop-down menu, select Once.

4. Click into the Execute Once On field. A calendar pop-up window is displayed. Select the desired date when you want the export to execute.

5. Within the calendar pop-up window, click the clock icon. Define the time when you want the export to execute.

6. From the Time Zone menu, select the time zone to use when determining the time to execute.

7. Click Save.

Executing on a Recurring Schedule

To schedule an Export Definition to execute on a recurring frequency:

1. At the top of the Export Definition Details screen, click Schedule.

2. Toggle Enabled to Yes.

3. From the Recurrence drop-down menu, select Repeat.

4. Next to Schedule Composer, select an interval:

   • 15 Minutes

   • 30 Minutes

   • Hour: Select the minute past the hour.

   • Day: Select the time of day.

   • Week: Select the day of the week, and the time of day.

   • Month: Select the day of the month, and the time of day.

   • Year: Select the day, the month, and the time of day.

5. As you configure the interval above, the CRON Expression field is automatically populated with the associated CRON expression. Optionally, to manually edit this expression, check Advanced Mode.

6. Optionally, using the From and To fields, define the export schedule begin and end date. If you leave these date fields blank, the schedule will run indefinitely.

7. From the Time Zone menu, select the time zone to use when determining the time to execute.

8. Click Save.

Email Notifications

You can configure the Export Definition to trigger success and / or failure notifications to one or more email recipients.

1. Optionally, if you want to send error notifications, toggle Error to Yes, then enter the email addresses for the notification recipients.

2. Optionally, if you want to send success notifications, toggle Success to Yes, then enter the email addresses for the notification recipients.

3. Click Save.